package 分省月度数据_全年抓取;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import entity.Table;
import okhttp3.*;
import org.apache.commons.io.FileUtils;
import org.apache.commons.io.IOUtils;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jsoup.Jsoup;
import org.jsoup.select.Elements;
import java.io.*;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.URLEncoder;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class 地区数据_分省月度数据_所有年份 {
    //      static String[]yearArray=new String[]{"1999","2000","2001","2002","2003","2004","2005","2006","2007","2008","2009","2010","2011","2012","2013","2014","2015", "2016", "2017", "2018", "2019"};
    static String[] yearArray = new String[]{"2017", "2018", "2019"};
    public static Map<String, String> provance_map = null;
    static String tunnelapiLink = "http://tunnel-api.apeyun.com/q?id=2120032600016801539&secret=ASySTfjht9VuRUh9&limit=1&format=txt&auth_mode=auto";
    public static Map<String, String> cname_code = new HashMap<>();

    public static void main(String[] args) throws IOException {
        getData();
    }

    private static void getData() throws IOException {
        //获取地区数据_分省月度数据id与名称键值对
        Map<String, String> table_id_name = getMap();
        provance_map = ProvanceJsonString.getProvance_map();//获取省份编码与名称键值对
        String url = "http://data.stats.gov.cn/easyquery.htm?cn=E0101";
        List<String> keyList = getKeySet(table_id_name);
        for (int n = 0; n < keyList.size(); n++) {
            //先获取第一层中的所有的key
            String areaId = keyList.get(n);//第一层id
            if (areaId.contains("A01")){
                continue;
            }
            String rootName = table_id_name.get(areaId);//第一层id名称
            Map<String, Table> yiJiDirectory = getTableId(areaId, url);
            List<String> provanceCodeList = getKeySet(provance_map);
            getDataTable(provanceCodeList, rootName, yiJiDirectory, url);
        }
    }

    /**
     * 使用猿人云代理更换ip
     *
     * @param zhimaLink
     * @return
     */
    private static String getIP(String zhimaLink) throws IOException {
        //获取IP的程序
        com.squareup.okhttp.Request request = new com.squareup.okhttp.Request.Builder().url(zhimaLink).build();
        com.squareup.okhttp.OkHttpClient client2 = new com.squareup.okhttp.OkHttpClient();
        com.squareup.okhttp.Response response = client2.newCall(request).execute();
        String html = new String(response.body().bytes(), "UTF-8");
        return html;
    }

    private static Map<String, Table> getTableId(String rootId, String url) throws IOException {
        OkHttpClient client = new OkHttpClient();//实现所有的HTTP的方法(Get,Post,Put,Head)
        Map<String, Table> tableIdname_map = new HashMap<>();
        RequestBody body = new FormBody.Builder()
                .add("id", rootId)
                .add("dbcode", "fsyd")
                .add("wdcode", "zb")
                .add("m", "getTree")
                .build();
        String cookies = "";
        while (true) {
            Request request = new Request.Builder()
                    .addHeader("Cookie", cookies)
                    .addHeader("User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/73.0.3683.103 Safari/537.36")
                    .addHeader("Accept", "text/plain, */*; q=0.01")
                    .url(url).post(body).build();
            Call call = client.newCall(request);
            Response execute = null;
            try {
                execute = call.execute();
                String string = execute.body().string();
                if (string.contains("请开启JavaScript并刷新该页") || string.contains("307 Temporary Redirect")) {
                    //先获取wzws_cid的值
                    cookies = requestIndex(client);
                    System.out.println(cookies);
                } else {
                    JSONArray jsonArray = JSON.parseArray(string);
                    for (int i = 0; i < jsonArray.size(); i++) {
                        JSONObject jsonObject = jsonArray.getJSONObject(i);
                        String tableId = jsonObject.getString("id");
                        String tableName = jsonObject.getString("name");
                        boolean isParent = jsonObject.getBoolean("isParent");
                        Table table = new Table();
                        table.setId(tableId);
                        table.setName(tableName);
                        table.setParent(isParent);
                        tableIdname_map.put(tableId, table);
                    }
                    return tableIdname_map;
                }
            } catch (IOException e) {
                e.printStackTrace();
                String IP_Info;
                try {
                    IP_Info = getIP(tunnelapiLink);
                } catch (Exception e1) {
                    continue;
                }
                String ip = IP_Info.split(":")[0];
                String host = IP_Info.split(":")[1];
                System.getProperties().setProperty("http.proxyHost", ip);
                System.getProperties().setProperty("http.proxyPort", host);
                continue;
            }
        }
    }

    private static void getDataTable(List<String> provanceCodeList, String dirName, Map<String, Table> map, String url) throws IOException {
        for (String id : map.keySet()) {
            Table table = map.get(id);
            boolean isParent = table.isParent();
            //当isParent为false，代表已经是表了，没有下一级
            if (!isParent) {
                int rowNumber = 0;
                String tableId = table.getId();
                String tableName = dirName + "/" + table.getName();
                File file = new File("E:\\程存淦_处理程序2020\\王佳艺\\CRED\\国家数据\\地区数据_分省月度数据_所有年份\\" + dirName);
                if (!file.exists()) {
                    file.mkdirs();//如果不存在就新建
                }
                //开始导出数据
                exportData(provanceCodeList, rowNumber, tableId, tableName);
            }
            if (isParent) {
                String tableId_er = table.getId();
                String tableName_er = dirName + "/" + table.getName();
                Map<String, Table> JiDirectory_er = getTableId(tableId_er, url);
                List<String> provanceCodeList_er = getKeySet(provance_map);
                for (String id_er : JiDirectory_er.keySet()) {
                    Table table_er = JiDirectory_er.get(id_er);
                    boolean isParent_er = table_er.isParent();
                    if (!isParent_er) {
                        int rowNumber_er = 0;
                        String tableId_er_er = table_er.getId();
                        String tableName_er_er = tableName_er + "/" + table_er.getName();
                        File file = new File("E:\\程存淦_处理程序2020\\王佳艺\\CRED\\国家数据\\地区数据_分省月度数据_所有年份\\" + tableName_er);
                        if (!file.exists()) {
                            file.mkdirs();//如果不存在就新建
                        }
                        //开始导出数据
                        exportData(provanceCodeList_er, rowNumber_er, tableId_er_er, tableName_er_er);
                    }
                }
            }
        }
    }

    /**
     * \s是指空白，包括空格、换行、tab缩进等所有的空白，而\S刚好相反，[\s\S]*是完全通配的意思
     * (.+)默认是贪婪匹配
     * 从eval( 到{}))的正则表达式
     */
    static Pattern compile = Pattern.compile("[\\s\\S]*(eval\\(.+[\\s\\S]*\\{\\}\\)\\))[\\s\\S]*");

    private static String requestWZWS(String html, String wzws_cid, OkHttpClient okHttpClient) throws IOException {
        Elements select = Jsoup.parse(html).select("[type=\"text/javascript\"]");
        String js = select.html();
        Matcher matcher = compile.matcher(js);
        String group = "";
        if (matcher.matches()) {
            group = matcher.group(1);
        }
        String s = invokeJsFromNode(group);
        String wzws_cid1 = null;
        while (true) {
            String url1 = "http://data.stats.gov.cn" + s;
            System.out.println(url1);
            Request request1 = new Request.Builder().url(url1)
                    .header("Cookie", wzws_cid)
                    .header("Referer", "http://data.stats.gov.cn/easyquery.htm?cn=E0103")
                    .header("Accept-Language", "zh-CN,zh;q=0.9")
                    .header("Connection", "keep-alive")
                    .header("User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/74.0.3729.131 Safari/537.36")
                    .get().build();
            Call call = okHttpClient.newCall(request1);
            Response execute = call.execute();
            String string = execute.body().string();
            System.out.println(string);
            wzws_cid1 = execute.header("Set-Cookie").split("path=/")[0];
            return wzws_cid1;
        }
    }

    public static String requestIndex(OkHttpClient client) {
        String url1 = "http://data.stats.gov.cn";
        System.out.println(url1);
        Request request1 = new Request.Builder().url(url1)
                .header("Cookie", "")
                .header("Accept", "text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3")
                .header("Accept-Language", "zh-CN,zh;q=0.9")
                .header("Connection", "keep-alive")
                .header("User-Agent", "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.100 Safari/537.36")
                .get().build();
        Call call = client.newCall(request1);
        Response execute = null;
        try {
            execute = call.execute();
            String string = execute.body().string();
            if (string.contains("请开启JavaScript并刷新该页") || string.contains("307 Temporary Redirect")) {
                //先获取wzws_cid的值
                String wzws_cid = execute.header("Set-Cookie").split("path=/")[0];
                wzws_cid = requestWZWS(string, wzws_cid, client);
                return wzws_cid;
            }
            System.out.println(string);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return "";
    }

    private static String invokeJsFromNode(String js) throws IOException {
        String s = IOUtils.toString(new FileInputStream("a.js"), "utf-8");
        String pathname = UUID.randomUUID() + ".js";//返回一个随机生成的UUID
        File file = new File(pathname);
        FileUtils.write(file, js + s);
        ProcessBuilder builder = new ProcessBuilder(
                "cmd.exe", "/c", "node " + pathname);
        builder.redirectErrorStream(true);
        Process p = null;
        try {
            p = builder.start();
        } catch (IOException e) {
            e.printStackTrace();
        }
        System.out.println(p);
//        String result = IOUtils.toString(p.getInputStream(), "utf-8");
        BufferedReader r = new BufferedReader(new InputStreamReader(p.getInputStream()));
        List<String> strings = null;
        try {
            strings = IOUtils.readLines(r);
        } catch (Exception e) {
            e.printStackTrace();
        }
        file.delete();
        return strings.get(1);
    }

    private static void exportData(List<String> provanceCodeList, int rowNumber, String tableKeyId, String tableName) {
        Workbook workbook1 = new XSSFWorkbook();
        int codeNum = 0;//记录code是第一个省份
        Sheet sheet1 = workbook1.createSheet("sheet");
        List<String> list = new ArrayList<>();
        List<String> headerList = new ArrayList<>();
        Map<String, Map<String, String>> dataMap = new HashMap<>();
        XSSFWorkbook workbook = new XSSFWorkbook();
        Row row1 = null;
        List<String> momeList = new ArrayList<>();
        for (int y = 0; y < yearArray.length; y++) {
            String year = null;
            year = yearArray[y];
            for (String code : provanceCodeList) {
                System.out.println(code);
                String contentUrl = "http://data.stats.gov.cn/easyquery.htm?m=QueryData&dbcode=fsyd&rowcode=zb&colcode=sj";
                String wdsStr = "[{\"wdcode\":\"reg\",\"valuecode\":\"" + code + "\"}]";
                String dfwdsStr = "[{\"wdcode\":\"zb\",\"valuecode\":\"" + tableKeyId + "\"},{\"wdcode\":\"sj\",\"valuecode\":\"" + "1999-2019" + "\"}]";
                String area = provance_map.get(code);
                String nianyue = null;
                JSONObject jsonObj = null;
                try {
                    String encode_wds = URLEncoder.encode(wdsStr, "UTF-8");//utf-8转码为endode
                    String encode_dfwds = URLEncoder.encode(dfwdsStr, "UTF-8");
                    contentUrl = contentUrl + "&wds=" + encode_wds + "&dfwds=" + encode_dfwds;//请求数据的链接
                    String jsonString = "";
                    int chongshi = 0;
                    while (true) {
                        jsonString = getContentJsonStr(contentUrl);
                        if (jsonString.contains("访问验证") || jsonString.contains("请开启JavaScript并刷新该页") || jsonString.equals("")) {
                            chongshi++;
                            //如果再再短短的10秒内重试了10次以上就休眠3分钟继续(因为ip在某个时间段不是很好)
                            if (chongshi > 10) {
                                try {
                                    System.out.println("休眠3分钟。。。。。。");
                                    Thread.sleep(180000);
                                } catch (InterruptedException e) {
                                    e.printStackTrace();
                                }
                            }
                            System.out.println("重试");
                            String IP_Info;
                            try {
                                IP_Info = getIP(tunnelapiLink);
                            } catch (Exception e1) {
                                continue;
                            }
                            String ip = null;
                            String host = null;
                            try {
                                ip = IP_Info.split(":")[0];
                                host = IP_Info.split(":")[1];
                            } catch (Exception e) {
                                e.printStackTrace();
                                continue;
                            }
                            System.getProperties().setProperty("http.proxyHost", ip);
                            System.getProperties().setProperty("http.proxyPort", host);
                            continue;
                        }
                        System.out.println(jsonString);//可能出现错误的json，重试此数据
                        try {
                            jsonObj = JSON.parseObject(jsonString);
                        } catch (Exception e) {
                            e.printStackTrace();
                            continue;
                        }
                        break;
                    }
                    JSONObject returndataObj = jsonObj.getJSONObject("returndata");
                    JSONArray wdnodesArray = returndataObj.getJSONArray("wdnodes");
                    JSONObject titleObj = wdnodesArray.getJSONObject(0);
                    JSONArray nodesArray = titleObj.getJSONArray("nodes");
                    //因为表头只需要记录一次，所以y=0和codeNum=0的时候记录即可
                    if (y == 0 && codeNum == 0) {
                        headerList.add("地区");//存第一行表头
                        headerList.add("年份");//存第一行表头
                        for (int i = 0; i < nodesArray.size(); i++) {
                            JSONObject oneTitleObj = nodesArray.getJSONObject(i);
                            String cname = oneTitleObj.getString("cname");//表字段名 如：社会消费品零售总额
                            String code1 = oneTitleObj.getString("code");//表字段名 如：A010301
                            String unit = oneTitleObj.getString("unit");//字段单位 如：亿元
                            String memo = oneTitleObj.getString("memo");//注解
                            if (memo.length() > 3) {
                                if (!momeList.contains(memo)) {
                                    momeList.add(memo);
                                }
                            }
                            String tableTitle;
                            if (unit.equals("")) {
                                tableTitle = cname;
                            } else {
                                tableTitle = cname + "/" + unit;

                            }
                            if (tableTitle.contains("(上年=100)")) {
                                tableTitle.replace("(上年=100)/", "(上年=100)/");
                            }
                            if (tableTitle.contains("帐款")) {
                                tableTitle.replace("帐款", "账款");
                            }
                            cname_code.put(code1, tableTitle);
                            headerList.add(tableTitle);//存第一行表头
                        }
                    }
                    codeNum++;
                    JSONArray dataArray = returndataObj.getJSONArray("datanodes");
                    for (int i = 0; i < dataArray.size(); i++) {
                        JSONObject oneObj = dataArray.getJSONObject(i);
                        JSONObject dataObj = oneObj.getJSONObject("data");//获取数据
                        JSONArray wds = oneObj.getJSONArray("wds");//获取月份
                        String valuecode = wds.getJSONObject(2).getString("valuecode");
                        String nian = valuecode.substring(0, 4) + "年";
                        String yue = valuecode.substring(4, 6) + "月";
                        nianyue = nian + yue;
                        String code1 = wds.getJSONObject(0).getString("valuecode");//获取字段
                        String cname = cname_code.get(code1);
                        String data = dataObj.get("data") + "";
                        String shuju = null;
                        if ("0".equals(data)) {//拿到数据
                            shuju = "";
                        } else {
                            int pointIndex = data.indexOf(".");
                            if (pointIndex > 0 && !data.contains("E")) {//判断以小数点进行截取大于0并且不包含数字科学计数法
                                String pointPart = data.substring(pointIndex + 1);//起始索引（包括）, 索引从 4 开始。
                                if (pointPart.length() > 2) {//判断以小数点截取大于2的情况
                                    Double doubleData = Double.parseDouble(data);
                                    BigDecimal bg = new BigDecimal(doubleData).setScale(2, RoundingMode.HALF_EVEN);
                                    shuju = bg.doubleValue() + "";
                                } else {
                                    shuju = data;
                                }
                            } else if (pointIndex < 0 && !data.contains("E")) {//正整数
                                shuju = data;
                            }
                            if (data.contains("E")) {
                                BigDecimal bd = new BigDecimal(data);
                                String data_edit = bd.toPlainString();//此方法返回此BigDecimal的字符串表示形式不带指数字段
                                shuju = data_edit;
                            }
                        }
                        String key = nianyue + "#" + area;//地区#年月#表字段
                        if (!list.contains(key)) {
                            list.add(key);
                        }
                        if (dataMap.get(key) == null) {//如果value为空
                            dataMap.put(key, new HashMap<String, String>());
                        }
                        Map<String, String> map = dataMap.get(key);
                        map.put(cname, shuju);//表头和数据
                    }
                    int rownum = 1;
                    Row row = sheet1.createRow(0);
                    if (rownum == 1) {
                        for (int i1 = 0; i1 < headerList.size(); i1++) {
                            row.createCell(i1).setCellValue(headerList.get(i1));
                        }
                    }
                    for (int i = 0; i < list.size(); i++) {
                        row1 = sheet1.createRow(rownum++);
                        String string = list.get(i);
                        String yearMonth = string.split("#")[0];//年月
                        String diqu = string.split("#")[1];//地区
                        Map<String, String> map = dataMap.get(string);
                        row1.createCell(0).setCellValue(diqu);
                        row1.createCell(1).setCellValue(yearMonth);
                        for (String s : map.keySet()) {
                            int i1 = headerList.indexOf(s);
                            try {
                                row1.createCell(i1).setCellValue(map.get(s));
                            } catch (Exception e) {
                                e.printStackTrace();
                            }
                        }
                    }

                } catch (UnsupportedEncodingException e) {
                    e.printStackTrace();
                }
            }
        }
        if (momeList.size() > 0) {
            int maxrow = sheet1.getLastRowNum() + 1;
            row1 = sheet1.createRow(maxrow);
            String memo = "注：";
            for (int j = 0; j < momeList.size(); j++) {
                memo = memo + (j + 1) + ". " + momeList.get(j).replace("[", "").replace("]", "").replace(",", "").replace("|", "") + " ";
            }
            row1.createCell(0).setCellValue(memo);
        }
        try {
            workbook1.write(new FileOutputStream("E:\\程存淦_处理程序2020\\王佳艺\\CRED\\国家数据\\地区数据_分省月度数据_所有年份\\" + tableName + ".xlsx"));
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static String getContentJsonStr(String contentUrl) {
        while (true){
            try {
                OkHttpClient client = new OkHttpClient();
                Request request = new Request.Builder().url(contentUrl).build();
                Response response = client.newCall(request).execute();
                String jsonStr = response.body().string();
                return jsonStr;
            } catch (IOException e) {
                e.printStackTrace();
            }
            return "";
        }
    }

    private static List<String> getKeySet(Map<String, String> map) {
        Set<String> keySet = map.keySet();//获取表格分类id_名称Map的key值 先转换为set集合
        Iterator<String> iter = keySet.iterator();//迭代器遍历
        List<String> keyList = new ArrayList<String>();
        while (iter.hasNext()) {//判断是够有下一个元素
            String areaId = iter.next();//获取集合元素
            keyList.add(areaId);
        }
        return keyList;
    }

    private static Map<String, String> getMap() {
        String jsonString = AreaJsonString.getJsonString();
        JSONArray jsonArray = JSON.parseArray(jsonString);
        Map<String, String> area_id_map = new HashMap<String, String>();
        for (int i = 0; i < jsonArray.size(); i++) {
            String arrayString = jsonArray.getString(i);//把java json数组转换成json对象，并转换成字符串
            JSONObject jsonObject = JSON.parseObject(arrayString);
            String id = jsonObject.getString("id");
            String name = jsonObject.getString("name");
            area_id_map.put(id, name);//键值对
        }
        return area_id_map;
    }
}
